﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Globalization;

namespace ResourceBlender_Express {
    /// <summary>
    /// A window which shows all identical translations for a resource
    /// </summary>
    public partial class WinDuplicateDetails : Window {
        private ObservableSortedDictionary<string, CultureInfo> languages;
        private string language, // the language of the translation for which to show dupes
                       translation; // the duplicate translation

        private DataTemplate ascSorted,
                             descSorted;
        private string crossTabSql; // used to refresh the grid, instead of rebuilding every time

        /// <summary>
        /// Initializes a new <see cref="WinDuplicateDetails"/>.
        /// </summary>
        /// <param name="languages">The languages for which to find duplicates.</param>
        /// <param name="resourceID">The resource ID.</param>
        public WinDuplicateDetails(ObservableSortedDictionary<string, CultureInfo> languages, string resourceLanguage, string resourceTranslation) {
            InitializeComponent();
            ascSorted = (DataTemplate)FindResource("HeaderTemplateAsc");
            descSorted = (DataTemplate)FindResource("HeaderTemplateDesc");
            this.languages = languages;
            this.language = resourceLanguage;
            this.translation = resourceTranslation;
            StringBuilder sqlBuffer = new StringBuilder();
            Database db = new Database();
            string crossTabColumns = db.GetCrosstabLanguageSql(languages);
            GridViewColumn col;
            foreach(KeyValuePair<string, CultureInfo> language in languages) {
                col = new GridViewColumn();
                col.Header = language.Value.EnglishName;
                col.DisplayMemberBinding = new Binding(language.Key);
                col.Width = 120;
                grdResources.Columns.Add(col);
            }
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    crossTabSql = string.Format(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, CAST(COUNT(e.resourceid) / (SELECT COUNT(resourceid) FROM {0}resources WHERE resourceid = tid)  AS SIGNED) AS numelements, {1}
                                           FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                                           LEFT JOIN {0}elements AS e ON r.resourceid = e.resourceid
                                           WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM {0}resources WHERE language = @lang AND translation = @translation)
                                           GROUP BY r.resourceid", db.TablePrefix, crossTabColumns);
                    break;
                case Database.DatabaseType.SQLServer:
                case Database.DatabaseType.SQLite:
                    crossTabSql = string.Format(@"SELECT r.resourceid AS tid, c.comment AS resourcecomment, COUNT(e.resourceid) / COALESCE(NULLIF((SELECT COUNT(r.resourceid) FROM {0}resources AS r WHERE r.resourceid = e.resourceid), 0), 1) AS numelements, {1}
                                          FROM {0}resources AS r LEFT JOIN {0}resourcecomments AS c ON r.resourceid = c.resourceid
                                          LEFT JOIN {0}elements AS e ON r.resourceid = e.resourceid
                                          WHERE r.resourceid IN (SELECT DISTINCT resourceid FROM {0}resources WHERE language = @lang AND translation = @translation)
                                          GROUP BY r.resourceid,
                                          c.comment,
                                          e.resourceid", db.TablePrefix, crossTabColumns);
                    break;
            }
            FindDuplicates();
        }

        /// <summary>
        /// Shows all languages in which a duplicate resource is available.
        /// </summary>
        private void FindDuplicates() {
            BackgroundWorker worker = new BackgroundWorker();
            worker.DoWork += (s, dwe) => {
                Database db = null;
                try {
                    if(db == null || !db.Connected) {
                        db = new Database();
                        db.OpenConnection();
                    }
                    using(Database.Command cmd = db.CreateCommand()) {
                        cmd.CommandText = crossTabSql;
                        cmd.AddParameterWithValue("@lang", language);
                        cmd.AddParameterWithValue("@translation", translation);
                        Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                        DataTable dtResults = new DataTable();
                        adapter.Fill(dtResults);
                        dwe.Result = dtResults;
                    }
                } catch(Exception ex) {
                    dwe.Result = ex.Message;
                } finally {
                    if(db != null && db.Connected) {
                        try {
                            db.CloseConnection();
                        } catch { }
                    }
                }
            };
            worker.RunWorkerCompleted += (s, rwe) => {
                SetStatus(null, false);
                if(rwe.Result is DataTable) {
                    lstGrid.DataContext = rwe.Result;
                } else {
                    DialogBox.ShowAlert(this, "Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error");
                }
            };
            SetStatus("Loading resources...", true);
            worker.RunWorkerAsync();
        }

        /// <summary>
        /// Determines whether the 'MergeDuplicates' command can be executed.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.CanExecuteRoutedEventArgs"/> instance containing the event data.</param>
        private void CanExecuteMergeDuplicates(object sender, CanExecuteRoutedEventArgs e) {
            e.CanExecute = this.IsLoaded && lstGrid.SelectedItems.Count > 1;
        }

        /// <summary>
        /// Merges the selected resources.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void MergeDuplicates(object sender, ExecutedRoutedEventArgs e) {
            BackgroundWorker worker = new BackgroundWorker();
            worker.DoWork += (s, dwe) => {
                Database db = null;
                try {
                    if(db == null || !db.Connected) {
                        db = new Database();
                        db.OpenConnection();
                    }
                    using(Database.Command cmd = db.CreateCommand()) {
                        List<string> iDsToMerge = dwe.Argument as List<string>;
                        Dictionary<string, RBTranslation> mergedResources = new Dictionary<string, RBTranslation>();
                        cmd.CommandText = string.Format("SELECT * FROM {0}resources WHERE{1}", db.TablePrefix, db.GenerateInClause("resourceid", iDsToMerge, false));
                        DbDataReader reader = cmd.ExecuteReader();
                        string language;
                        while(reader.Read()) {
                            language = reader["language"].ToString();
                            if(!mergedResources.ContainsKey(language)) {
                                mergedResources.Add(language,new RBTranslation(language, reader["translation"].ToString(), reader["translatorcomment"].ToString()));
                            }
                        }
                        reader.Close();
                        
                        switch(db.InstanceType) {
                            case Database.DatabaseType.MySQL:
                                cmd.CommandText = string.Format("INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans,@translatorcomment) ON DUPLICATE KEY UPDATE translation = @trans", db.TablePrefix);
                                break;
                            case Database.DatabaseType.SQLServer:
                                cmd.CommandText = string.Format("UPDATE {0}resources SET translation = @trans, translatorcomment = @translatorcomment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @translatorcomment)", db.TablePrefix);
                                break;
                            case Database.DatabaseType.SQLite:
                                cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans,@translatorcomment)", db.TablePrefix);
                                break;
                        }
                        cmd.AddParameterWithValue("@tID", iDsToMerge[0]); // merge all into the first ID which was selected, re-assign the rest to this ID later
                        cmd.AddParameterWithValue("@lang", "");
                        cmd.AddParameterWithValue("@trans", "");
                        cmd.AddParameterWithValue("@translatorcomment", "");
                        StringBuilder errors = new StringBuilder();
                        foreach(KeyValuePair<string, RBTranslation> translation in mergedResources) {
                            cmd.Parameters["@lang"].Value = translation.Value.LanguageCode;
                            cmd.Parameters["@trans"].Value = translation.Value.Translation;
                            if(translation.Value.Comment == null) {
                                cmd.Parameters["@translatorcomment"].Value = DBNull.Value;
                            } else {
                                cmd.Parameters["@translatorcomment"].Value = translation.Value.Comment;
                            }
                            try {
                                cmd.ExecuteNonQuery();
                            } catch(Exception ex) {
                                errors.AppendFormat("Failed to merge translation for {0} - '{1}': {2}\n", translation.Key, translation.Value, ex.Message);
                            }
                        }
                        if(errors.Length > 0) {
                            errors.Append("Errors encountered, skipping re-assigning elements\n");
                        } else {
                            cmd.CommandText = "UPDATE " + db.TablePrefix + "elements SET resourceid = @tID WHERE resourceid = @oldTId";
                            cmd.Parameters["@tID"].Value = iDsToMerge[0];
                            cmd.AddParameterWithValue("@oldTId", "");
                            for(int i = 1; i < iDsToMerge.Count; i++) {
                                cmd.Parameters["@oldTId"].Value = iDsToMerge[i];
                                try {
                                    cmd.ExecuteNonQuery();
                                } catch(Exception ex) {
                                    errors.AppendFormat("Failed to re-assign elements from translation #{0} to #{1}: {2}\n", iDsToMerge[0], iDsToMerge[i], ex.Message);
                                }
                            }
                            if(errors.Length > 0) {
                                errors.Append("Errors encountered, skipping re-assigning elements\n");
                            }
                        }
                        if(errors.Length > 0) {
                            dwe.Result = errors.ToString();
                        }
                    }
                } catch(Exception ex) {
                    dwe.Result = ex.Message;
                } finally {
                    if(db != null && db.Connected) {
                        try {
                            db.CloseConnection();
                        } catch { }
                    }
                }
            };
            worker.RunWorkerCompleted += (s, rwe) => {
                SetStatus(null, false);
                if(rwe.Result != null) {
                    DialogBox.ShowAlert(this, "Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error");
                }
                FindDuplicates();
            };
            SetStatus("Loading...", true);
            List<string> selectedIds = new List<string>();
            foreach(DataRowView rowView in lstGrid.SelectedItems) {
                selectedIds.Add(rowView.Row.ItemArray[0].ToString());
            }
            worker.RunWorkerAsync(selectedIds);
        }

        /// <summary>
        /// Shows the elements used by a resource.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void ExpandDuplicateDetails(object sender, RoutedEventArgs e) {
            TreeViewItem treElementDetails = e.OriginalSource as TreeViewItem;
            TreeViewItem defaultNode = treElementDetails.Items[0] as TreeViewItem;
            if(defaultNode.Tag == null) {
                return;
            }
            BackgroundWorker worker = new BackgroundWorker();
            worker.DoWork += (s, dwe) => {
                Database db = null;
                try {
                    if(db == null || !db.Connected) {
                        db = new Database();
                        db.OpenConnection();
                    }
                    using(Database.Command cmd = db.CreateCommand()) {
                        cmd.CommandText = string.Format(@"SELECT a.displayname AS app, b.name AS bundle, e.elementname AS element FROM {0}elements AS e 
                                                          INNER JOIN {0}bundles as b ON bundle = b.id INNER JOIN {0}applications AS a ON a.id = b.application 
                                                          WHERE resourceid = @resourceId ORDER BY displayname, bundle, elementname", db.TablePrefix);
                        cmd.AddParameterWithValue("@resourceId", dwe.Argument);
                        DbDataReader reader = cmd.ExecuteReader();
                        string app,
                               bundle,
                               element,
                               lastApp = string.Empty,
                               lastBundle = string.Empty,
                               lastElement = string.Empty;
                        Dictionary<string, List<string>> applications = new Dictionary<string,List<string>>();
                        while(reader.Read()) {
                            app = reader["app"].ToString();
                            bundle = reader["bundle"].ToString();
                            element = reader["element"].ToString();
                            if(app != lastApp) {
                                lastApp = app;
                                applications.Add(app, new List<string>());
                            }
                            applications[app].Add(string.Format("{0} - {1}", bundle, element));
                        }
                        dwe.Result = applications;
                    }
                } catch(Exception ex) {
                    dwe.Result = ex.Message;
                } finally {
                    if(db != null && db.Connected) {
                        try {
                            db.CloseConnection();
                        } catch { }
                    }
                }
            };
            worker.RunWorkerCompleted += (s, rwe) => {
                SetStatus(null, false);
                if(rwe.Result is string) {
                    DialogBox.ShowAlert(this, "Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error");
                } else {
                    Dictionary<string, List<string>> applications = rwe.Result as Dictionary<string, List<string>>;
                    if(applications.Count > 0) {
                        treElementDetails.Items.Clear();
                        foreach(KeyValuePair<string, List<string>> app in applications) {
                            TreeViewItem itmApp = new TreeViewItem();
                            itmApp.Header = app.Key;
                            itmApp.FontWeight = FontWeights.Bold;
                            treElementDetails.Items.Add(itmApp);
                            foreach(string element in app.Value) {
                                TreeViewItem itmElement = new TreeViewItem();
                                itmElement.Header = string.Format(element);
                                itmApp.FontWeight = FontWeights.Normal;
                                itmApp.Items.Add(itmElement);
                            }
                        }
                    } else {
                        defaultNode.Tag = null;
                    }
                }
            };
            SetStatus("Loading...", true);
            worker.RunWorkerAsync(treElementDetails.Tag);
        }

        /// <summary>
        /// Determines whether all resources can be selected.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.CanExecuteRoutedEventArgs"/> instance containing the event data.</param>
        private void CanExecuteSelectAll(object sender, CanExecuteRoutedEventArgs e) {
            e.CanExecute = this.IsLoaded && lstGrid.HasItems;
        }

        /// <summary>
        /// Selects all resources.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void SelectAllResources(object sender, ExecutedRoutedEventArgs e) {
            lstGrid.SelectAll();
        }

        /// <summary>
        /// Sorts the grid.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.RoutedEventArgs"/> instance containing the event data.</param>
        private void OnColumnHeaderClick(object sender, RoutedEventArgs e) {
            if(e.OriginalSource is GridViewColumnHeader) { // sort the grid
                GridViewColumn column = ((GridViewColumnHeader)e.OriginalSource).Column;
                string sortPath = column.DisplayMemberBinding == null ? (column.CellTemplate.DataType ?? string.Empty).ToString() : (column.DisplayMemberBinding as Binding).Path.Path;
                if(sortPath.Length == 0) {
                    return; // no field specified
                }
                if(column.HeaderTemplate == ascSorted) {
                    column.HeaderTemplate = null;
                    (lstGrid.DataContext as DataTable).DefaultView.Sort = sortPath + " DESC"; ;
                } else if(column.HeaderTemplate == descSorted) {
                    column.HeaderTemplate = ascSorted;
                    (lstGrid.DataContext as DataTable).DefaultView.Sort = "";
                } else {
                    column.HeaderTemplate = ascSorted;
                    (lstGrid.DataContext as DataTable).DefaultView.Sort = sortPath + " ASC";
                }
            }
        }

        #region Editing and Deleting
        /// <summary>
        /// Determines whether the EditResource or DeleteResource commands can be executed.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.CanExecuteRoutedEventArgs"/> instance containing the event data.</param>
        private void CanEditOrDelete(object sender, CanExecuteRoutedEventArgs e) {
            e.CanExecute = this.IsLoaded && lstGrid.SelectedValue != null;
        }

        /// <summary>
        /// Fires the EditResource command when the grid is double clicked.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.RoutedEventArgs"/> instance containing the event data.</param>
        private void FireEditResource(object sender, RoutedEventArgs e) {
            if(lstGrid.SelectedValue != null) {
                ResourceBlenderCommands.Edit.Execute(null, null);
            }
        }

        /// <summary>
        /// Opens a dialog to edits the selected resource (or first selected resource if multiple items are selected).
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void EditResource(object sender, ExecutedRoutedEventArgs e) {
            int resourceId = -1;
            if(!int.TryParse(lstGrid.SelectedValue.ToString(), out resourceId)) {
                DialogBox.ShowAlert(this, "Error #459: Invalid resource", "Error");
            } else {
                WinAddResource dlg = new WinAddResource(resourceId);
                dlg.Owner = this;
                dlg.ShowDialog();
                FindDuplicates();
            }
        }

        /// <summary>
        /// Shows a prompt to confirm deletion of a resource, then deletes it from the database.
        /// </summary>
        /// <param name="sender">The sender.</param>
        /// <param name="e">The <see cref="System.Windows.Input.ExecutedRoutedEventArgs"/> instance containing the event data.</param>
        private void DeleteResource(object sender, ExecutedRoutedEventArgs e) {
            if(lstGrid.SelectedItem == null) {
                return;
            }
            BackgroundWorker worker = new BackgroundWorker();
            worker.DoWork += (s, dwe) => {
                Database db = null;
                try {
                    if(db == null || !db.Connected) {
                        db = new Database();
                        db.OpenConnection();
                    }
                    using(Database.Command cmd = db.CreateCommand())
                    using(Database.Command cmdDeleteComment = db.CreateCommand()) {
                        List<int> resourceIds = dwe.Argument as List<int>;
                        cmd.CommandText = string.Format("DELETE FROM {0}resources WHERE resourceid = @rID", db.TablePrefix);
                        cmdDeleteComment.CommandText = string.Format("DELETE FROM {0}resourcecomments WHERE resourceid = @rID", db.TablePrefix);
                        cmd.AddParameterWithValue("@rID", string.Empty);
                        cmdDeleteComment.AddParameterWithValue("@rID", string.Empty);
                        foreach(int id in resourceIds) {
                            cmd.Parameters["@rID"].Value = id;
                            cmdDeleteComment.Parameters["@rID"].Value = id;
                            cmd.ExecuteNonQuery();
                            cmdDeleteComment.ExecuteNonQuery();
                        }
                    }
                } catch(Exception ex) {
                    dwe.Result = ex.Message;
                } finally {
                    if(db != null && db.Connected) {
                        try {
                            db.CloseConnection();
                        } catch { }
                    }
                }
            };
            worker.RunWorkerCompleted += (s, rwe) => {
                SetStatus(null, false);
                if(rwe.Result != null) {
                    DialogBox.ShowAlert(this, "Error: Could not connect to database - " + rwe.Result.ToString(), "Database Error");
                }
                FindDuplicates();
            };
            SetStatus("Deleting...", true);
            List<int> resourcesToDelete = new List<int>();
            foreach(DataRowView rowView in lstGrid.SelectedItems) {
                try {
                    int numElements = int.Parse(rowView.Row.ItemArray[2].ToString()),
                        resourceId = int.Parse(rowView.Row.ItemArray[0].ToString());
                    if(numElements > 0) {
                        DialogBox.ShowAlert(this, string.Format("Resource #{0} is currently in use and must be merged with another translation before deletion.", resourceId), "Error");
                    } else {
                        resourcesToDelete.Add(resourceId);
                    }
                } catch {
                    DialogBox.ShowAlert(this, "Error deleting resource", "Error");
                }
            }
            if(resourcesToDelete.Count > 0 && DialogBox.Show(this, string.Format("{0} {1} will be deleted.\n\nAre you sure?", resourcesToDelete.Count.ToString(), resourcesToDelete.Count == 1 ? "resource" : "resources"), "Confirm Delete", DialogBox.DialogBoxType.YesNo, DialogBox.DialogBoxIcon.Warning, DialogBox.DialogBoxButton.No) == DialogBox.DialogBoxButton.Yes) {
                worker.RunWorkerAsync(resourcesToDelete);
            }
        }
        #endregion

        /// <summary>
        /// Sets the text on the status message.
        /// </summary>
        /// <param name="message">The message.</param>
        /// <param name="showProgress"><c>true</c> if the progress bar should be shown, otherwise <c>false</c>.</param>
        private void SetStatus(string message, bool showProgress) {
            prgStatus.Visibility = showProgress ? Visibility.Visible : Visibility.Hidden;
            if(message == null) {
                lblStatus.Visibility = Visibility.Hidden;
            } else {
                lblStatus.Visibility = Visibility.Visible;
                lblStatus.Content = message;
            }
        }
    }
}
